import db from "../../db.js";
const save = async (data) => {
  const [result] = await db.query(
    "INSERT INTO `fields`(`name`,`type`,`label`,`model_id`) values?",
    [data]
  );
  if (result?.affectedRows > 0) {
    return result?.affectedRows;
  }
  return false;
};
const del = async (id) => {
  const [result] = await db.query(
    "update   `fields` set isDelete=1 where id=?",
    id
  );
  if (result?.affectedRows > 0) {
    return true;
  }
  return false;
};
const delByModelId = async (modelId) => {
  const [result] = await db.query(
    "update   `fields` set isDelete=1 where model_id=?",
    modelId
  );
  if (result?.affectedRows > 0) {
    return true;
  }
  return false;
};
//UPDATE mytable SET
// myfield = CASE id
// WHEN 1 THEN 'value'
// WHEN 2 THEN 'value'
// WHEN 3 THEN 'value'
// END
// WHERE id IN (1,2,3)
//item?.name, item?.type, item?.label
// UPDATE categories SET
//     display_order = CASE id
//         WHEN 1 THEN 3
//         WHEN 2 THEN 4
//         WHEN 3 THEN 5
//     END,
//     title = CASE id
//         WHEN 1 THEN 'New Title 1'
//         WHEN 2 THEN 'New Title 2'
//         WHEN 3 THEN 'New Title 3'
//     END
// WHERE id IN (1,2,3)

const update = async (list) => {
  let sqlList = [];
  const keys = ["name", "type", "label"];
  let ids = list?.map((item) => item?.fieldId);
  keys.forEach((key) => {
    let sql = "";
    sql = `${key} = CASE id `;
    list.forEach((item) => {
      sql += `WHEN '${item?.fieldId}' THEN '${item?.[key]}'`;
    });
    sql += " END";
    sqlList.push(sql);
  });

  const [result] = await db.query(
    `update fields set ${sqlList.join(",")} where id in (${ids.join(",")})`
  );
  if (result?.affectedRows > 0) {
    return true;
  }
  return false;
};
const getList = async () => {
  const [rows] = await db.query(
    "select fields.id,fields.name,fields.type,fields.label,model.name as modelName,model.id as modelId from `fields` LEFT JOIN model on fields.model_id=model.id where fields.isDelete=0"
  );
  return rows || [];
};
const findByIds = async (ids) => {
  const [rows] = await db.query(
    "select id,name,type,label from `fields` where  id IN (?) and isDelete=0 ",
    [ids]
  );
  return rows || [];
};
const findById = async (id) => {
  const [rows] = await db.query(
    "select id,name,type,label from `fields` where isDelete=0 and id=? limit 1",
    id
  );
  return rows || [];
};
export default {
  save,
  del,
  getList,
  findById,
  findByIds,
  update,
  delByModelId,
};
